﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

namespace SindSaudeCamadas.Dados
{
    public class Noticias
    {
        Conex conexao = new Conex();
        public DataTable carregaNoticias()
        {
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            MySqlDataAdapter daSQL = new MySqlDataAdapter();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "select *, case when flagOrdem = 1 then 'Sim' else 'Não' end as carrocel from tbnoticias WHERE IDTIPO = 0 order by datNoticia desc ;";
                
                cmdSQL.Connection.Open();
                daSQL.SelectCommand = cmdSQL;
                daSQL.Fill(dt);
            }
            catch (Exception)
            {                
                throw;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
                daSQL.Dispose();
            }
            return dt;
        }
        public DataTable carregaNoticiasCaixa()
        {
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            MySqlDataAdapter daSQL = new MySqlDataAdapter();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "SELECT *,id,strnome FROM tbnoticias where idTipo = 0 order by datNoticia desc limit 2;";

                cmdSQL.Connection.Open();
                daSQL.SelectCommand = cmdSQL;
                daSQL.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
                daSQL.Dispose();
            }
            return dt;
        }
        public DataTable carregaOutrosNoticias()
        {
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            MySqlDataAdapter daSQL = new MySqlDataAdapter();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "SELECT *,cast(concat('NoticiasCompleto.aspx?a=', id) as char) as url FROM tbnoticias where idtipo = 0 order by datNoticia desc limit 12;";

                cmdSQL.Connection.Open();
                daSQL.SelectCommand = cmdSQL;
                daSQL.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
                daSQL.Dispose();
            }
            return dt;
        }
        public DataTable carregaNoticiaCarrocel()
        {
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            MySqlDataAdapter daSQL = new MySqlDataAdapter();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "SELECT a.strNome,strSub_Nome,a.id,a.strCaminho_Imagem,cast(concat('NoticiasCompleto.aspx?a=', a.id) as char) as url FROM tbnoticias a where a.idTipo = 0  order by datNoticia desc limit 6;";
                
                cmdSQL.Connection.Open();
                daSQL.SelectCommand = cmdSQL;
                daSQL.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
                daSQL.Dispose();
            }
            return dt;
        }
        public DataTable carregaNoticia(int id)
        {
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            MySqlDataAdapter daSQL = new MySqlDataAdapter();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "SELECT * FROM tbnoticias WHERE id = @ID;";
                cmdSQL.Parameters.AddWithValue("@ID", id);

                cmdSQL.Connection.Open();
                daSQL.SelectCommand = cmdSQL;
                daSQL.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
                daSQL.Dispose();
            }
            return dt;
        }
        public bool alteraNoticias(int id, string strNome, String strConteudo, int idTipo, String strSub_Nome, string strCaminho_Imagem, int ordem, string strdata, DateTime datNoticias)
        {
            bool altera = false;
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();   
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "UPDATE tbnoticias SET ID = @ID, STRNOME = @STRNOME, STRCONTEUDO = @STRCONTEUDO, IDTIPO = @IDTIPO,STRSUB_NOME = @STRSUB_NOME,STRCAMINHO_IMAGEM = @STRCAMINHO_IMAGEM ,FLAGORDEM = @FLAGORDEM,STRDATA = @STRDATA,DATNOTICIA = @DATNOTICIAS WHERE ID = @ID;";
                cmdSQL.Parameters.AddWithValue("@ID", id);
                cmdSQL.Parameters.AddWithValue("@STRNOME", strNome);
                cmdSQL.Parameters.AddWithValue("@STRCONTEUDO", strConteudo);
                cmdSQL.Parameters.AddWithValue("@idTIPO", idTipo);
                cmdSQL.Parameters.AddWithValue("@STRSUB_NOME",strSub_Nome);
                cmdSQL.Parameters.AddWithValue("@STRCAMINHO_IMAGEM", strCaminho_Imagem);
                cmdSQL.Parameters.AddWithValue("@FLAGORDEM", ordem);
                cmdSQL.Parameters.AddWithValue("@STRDATA", strdata);
                cmdSQL.Parameters.AddWithValue("@DATNOTICIAS", datNoticias);

                cmdSQL.Connection.Open();
                cmdSQL.ExecuteNonQuery();
                altera = true;   
            }
            catch (Exception)
            {
                altera = false;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
            }
            return altera;
        }
        public bool insereNoticias(string strNome, String strConteudo, int idTipo, String strSub_Nome, string strCaminho_Imagem, int ordem, string strdata, DateTime datNoticia)
        {
            bool insere = false;
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "INSERT INTO tbnoticias(STRNOME,STRCONTEUDO,IDTIPO,STRSUB_NOME,STRCAMINHO_IMAGEM,FLAGORDEM,STRDATA,DATNOTICIA)VALUES(@STRNOME,@STRCONTEUDO,@IDTIPO,@STRSUB_NOME,@STRCAMINHO_IMAGEM,@FLAGORDEM,@STRDATA,@DATNOTICIAS);";
                cmdSQL.Parameters.AddWithValue("@STRNOME", strNome);
                cmdSQL.Parameters.AddWithValue("@STRCONTEUDO", strConteudo);
                cmdSQL.Parameters.AddWithValue("@idTIPO", idTipo);
                cmdSQL.Parameters.AddWithValue("@STRSUB_NOME", strSub_Nome);
                cmdSQL.Parameters.AddWithValue("@STRCAMINHO_IMAGEM", strCaminho_Imagem);
                cmdSQL.Parameters.AddWithValue("@FLAGORDEM", ordem);
                cmdSQL.Parameters.AddWithValue("@STRDATA", strdata);
                cmdSQL.Parameters.AddWithValue("@DATNOTICIAS", datNoticia);

                cmdSQL.Connection.Open();
                cmdSQL.ExecuteNonQuery();
                insere = true;
            }
            catch (Exception)
            {
                 insere = false;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
            }            
            return insere;
        }
        public bool deleteNoticias(int id)
        {
            bool delete = false;
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "UPDATE tbnoticias SET IDTIPO = 1 WHERE ID = @ID;";
                cmdSQL.Parameters.AddWithValue("@ID", id);

                cmdSQL.Connection.Open();
                cmdSQL.ExecuteNonQuery();
                delete = true;
            }
            catch (Exception)
            {
                delete = false;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
            }
            return delete;
        }
        public int QtdCarrossel()
        {
            int intcount = 0;
            DataTable dt = new DataTable();
            MySqlCommand cmdSQL = new MySqlCommand();
            try
            {
                string conex = ConfigurationManager.AppSettings["SindSaude"].ToString();
                cmdSQL.Connection = conexao.retornaConexao(conex);

                cmdSQL.CommandType = CommandType.Text;
                cmdSQL.CommandText = "select count(*) from tbnoticias where flagOrdem = 1;";
                
                cmdSQL.Connection.Open();
                intcount = Convert.ToInt16(cmdSQL.ExecuteScalar());
                
            }
            catch (Exception)
            {
                intcount = 0;
            }
            finally
            {
                cmdSQL.Connection.Dispose();
                cmdSQL.Dispose();
            }
            return intcount;
        }
    }
}
